IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sp_PhieuXuatNhap_GetHoaDonXuatBanHangHoaByMaxId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_PhieuXuatNhap_GetHoaDonXuatBanHangHoaByMaxId]
GO

CREATE PROC [sp_PhieuXuatNhap_GetHoaDonXuatBanHangHoaByMaxId]
AS
BEGIN
	DECLARE @Max_id	INT
	DECLARE @No_cu	INT
	DECLARE @Khach_hang_cua_max_id	INT

	SET @Max_id = (SELECT MAX(id) FROM PhieuNhapXuat WHERE Loai_nhap_xuat = 0 OR Loai_nhap_xuat = 1)
	SET @Khach_hang_cua_max_id = (SELECT Ma_khach_hang FROM PhieuNhapXuat WHERE id = @Max_id AND (Loai_nhap_xuat = 0 OR Loai_nhap_xuat = 1))
	IF (SELECT COUNT(Ma_khach_hang) FROM CongNo WHERE Ma_khach_hang = @Khach_hang_cua_max_id) = 0
		SET @No_cu = 0
	ELSE
		SET @No_cu = (SELECT Thanh_tien FROM CongNo WHERE Ma_khach_hang = @Khach_hang_cua_max_id)
		
	SELECT
		A.id AS 'ID',
		A.Ki_hieu AS 'Ki_hieu', 
		B.Ten AS 'Ten_KH', 
		B.Dia_chi AS 'Dia_chi', 
		B.Ma_so_thue AS 'Ma_so_thue', 
		B.Tel AS 'Dien_thoai',
		E.Ten AS 'Hinh_thuc_thanh_toan', 
		G.Ki_hieu AS 'Don_vi_tien_te',
		D.Ma_hang AS 'Ma_hang', 
		D.Ten AS 'Ten_hang', 
		F.Don_vi_goc AS 'Don_vi_tinh', 
		C.So_luong AS 'So_luong',
		D.Gia_ban_le AS 'Don_gia', 
		C.Thanh_tien AS 'Thanh_tien_truoc_VAT',
		C.Thue AS 'VAT', 
		C.Tien_thue AS 'Tien_VAT', 
		C.Tien_chiet_khau AS 'Tien_CK',
		A.Thanh_toan AS 'Thanh_toan',
		A.Tien_no AS 'No_moi',
		@No_cu AS 'No_cu'
	FROM PhieuNhapXuat AS A INNER JOIN KhachHang AS B ON A.Ma_khach_hang = B.id
		INNER JOIN ChiTietNhapXuat AS C ON A.id = C.Ma_phieu_nhap_xuat
		INNER JOIN HangHoa AS D ON D.id = C.Ma_hang_hoa
		INNER JOIN HinhThucThanhToan AS E ON E.id = A.Ma_hinh_thuc_thanh_toan
		INNER JOIN DonViQuiDoi AS F ON F.id = D.Ma_don_vi_qui_doi
		INNER JOIN NgoaiTe AS G ON G.id = A.Ma_ngoai_te
	WHERE A.ID = @Max_id
END

